package cn.com.libertymutual.sp.dao;

import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import cn.com.libertymutual.sp.bean.TbSpOrder;

@Repository
public interface OrderDao extends PagingAndSortingRepository<TbSpOrder, Integer>, JpaSpecificationExecutor<TbSpOrder> {

	@Transactional
	@Modifying
	@Query("update TbSpOrder set status = ?2,orderDetail = ?3  where paymentNo = ?1")
	void updateStatus(String paymentNo, String status, String policy);

	List<TbSpOrder> findByPaymentNo(String paymentNo);

	List<TbSpOrder> findByPolicyNo(String policyNo);

	List<TbSpOrder> findByProposalNo(String proposalNo);

	@Query("from TbSpOrder where proposalNo =?1")
	List<TbSpOrder> findProposalNo(String proposalNo);

	/**根据用户编号查询订单，注意合并账户的编号有2个*/
	@Query("from TbSpOrder where userId = :userCode or userId = (select userCodeBs from TbSpUser where userCode = :userCode)")
	List<TbSpOrder> findAllByUserId(@Param("userCode") String userCode);

	/**根据用户编号查询订单，注意合并账户的编号有2个*/
	@Query("from TbSpOrder o  where o.policyNo<>'' and startDate>=now() and endDate<=now() and "
			+ "(userId = :userCode or userId = (select userCodeBs from TbSpUser where userCode = :userCode))")
	List<TbSpOrder> findPolicyByUserId(@Param("userCode") String userCode);

	@Query("from TbSpOrder o  where o.policyNo ='' and proposalNo<>'' and startDate>=now() and endDate<=now() and "
			+ "(userId =  ?1 or userId = (select userCodeBs from TbSpUser where userCode =  ?1))")
	List<TbSpOrder> findProposalByUserId(String userCode);

	@Query("from TbSpOrder o  where startDate>=now() and endDate<=now() and  idNo=?1 and status=?2")
	List<TbSpOrder> findNoPayId(String id, String status);

	@Query(" from TbSpOrder  where applicantName =?1  and policyNo =?2")
	List<TbSpOrder> findOrderName(String data, String policyNo);

	@Query(" from TbSpOrder  where idNo =?1  and policyNo =?2")
	List<TbSpOrder> findOrderIdNo(String data, String idNo);

	// @Query(value = "select count(*) from tb_sp_order where ( USER_ID=?1 or
	// Referee_ID=?1 or USER_ID=?2 or Referee_ID=?2 ) and Policy_No <>''",
	// nativeQuery = true)
	// int findUserCount(String userCode,String userCodeBs);

	@Query(value = "select count(*) from tb_sp_order  where ( Referee_ID=?1 or Referee_ID=?2 or (Referee_ID = ''  and USER_ID=?1) or (Referee_ID = ''  and USER_ID=?2) ) and Policy_No <>''", nativeQuery = true)
	int findUserCount(String userCode, String userCodeBs);

	@Query(value = "select count(*) from tb_sp_order  where PRODUCT_ID = :productId and UPDATE_TIME between :startDate and :endDate group by PRODUCT_ID", nativeQuery = true)
	int findSalesCountByDate(@Param("productId") String productId, @Param("startDate") String startDate, @Param("endDate") String endDate);

	@Query(value = "select count(*)  from tb_sp_order  where PRODUCT_ID = :productId ", nativeQuery = true)
	int findSalesCount(@Param("productId") String productId);

	@Query(value = "select DISTINCT(PRODUCT_ID)  from tb_sp_order where UPDATE_TIME between :startDate and :endDate", nativeQuery = true)
	List<String> findDistinctProduct(@Param("startDate") String startDate, @Param("endDate") String endDate);

	@Query(value = "select DISTINCT(PRODUCT_ID)  from tb_sp_order", nativeQuery = true)
	List<String> findDistinctProduct();

	@Query(" from TbSpOrder o where  policyNo <>'' and ( userId= ?1 or refereeId = ?1 or userId= ?2 or refereeId = ?2 ) and DATE_FORMAT(o.createTime,'%Y%m')=?3 ORDER BY o.createTime desc")
	List<TbSpOrder> performanceList(String userCode, String userCodeBs, String queryParam);

	// @Query(" from TbSpOrder o where policyNo <>'' and ( userId= ?1 or refereeId =
	// ?1 ) and DATE_FORMAT(o.createTime,'%Y%m')=?2 and riskCode = ?3 ORDER BY
	// o.createTime desc")
	// List<TbSpOrder> performanceList(String userCode,
	// String queryParam, String riskCode);

	@Query(" from TbSpOrder o where policyNo <>'' and (  userId= ?1 or refereeId = ?1 or userId= ?2 or refereeId = ?2 ) and DATE_FORMAT(o.createTime,'%Y')=?3  ORDER BY o.createTime desc")
	List<TbSpOrder> performanceYear(String userCode, String userCodeBs, String queryParam);

	@Query(" from TbSpOrder o where policyNo <>'' and (  userId= ?1 or refereeId = ?1 ) and DATE_FORMAT(o.createTime,'%Y%m%d')=?2  ORDER BY o.createTime desc")
	List<TbSpOrder> performanceDay(String userCode, String queryParam);

	// @Query(value = "select PRODUCT_ID,RISK_CODE,COUNT(PRODUCT_ID) from
	// tb_sp_order GROUP BY PRODUCT_ID", nativeQuery = true)
	// List<Object> findOrderCount();
	// @Query(value="select o.*,t.* from ("+
	// "select PRODUCT_ID,COUNT(PRODUCT_ID) from tb_sp_order where UPDATE_TIME
	// between :startDate and :endDate GROUP BY PRODUCT_ID) o,"+
	// "(SELECT * from tb_sp_product where id in("+
	// "select DISTINCT(PRODUCT_ID) from tb_sp_order where UPDATE_TIME between
	// :startDate and :endDate )) t where o.PRODUCT_ID = t.id",nativeQuery=true)
	// List<Object> findOrderCountByDate(@Param("startDate") String startDate,
	// @Param("endDate") String endDate);
	@Query("from TbSpOrder o  where  relationId=?1")
	List<TbSpOrder> findRelationOrder(String relationId);
}
